"""
实验结果数据库模型
"""
from sqlalchemy import Column, Integer, String, Text, DateTime, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import relationship
from datetime import datetime

from ..base import Base


class ExperimentRun(Base):
    """实验运行记录表"""
    __tablename__ = "experiment_runs"
    
    id = Column(Integer, primary_key=True, index=True)
    project_id = Column(Integer, ForeignKey("projects.id", ondelete="CASCADE"), nullable=False, index=True)
    name = Column(String(200), comment="实验名称（可选，默认自动生成）")
    description = Column(Text, comment="实验描述")
    snapshot_file_path = Column(Text, comment="参数快照JSON文件路径")
    created_at = Column(DateTime, default=lambda: datetime.now(), comment="创建时间")
    
    # 关系
    project = relationship("Project", backref="experiment_runs")
    results = relationship("ExperimentResult", back_populates="experiment_run", cascade="all, delete-orphan")
    categories = relationship("ExperimentDataCategory", back_populates="experiment_run", cascade="all, delete-orphan")
    
    # 索引
    __table_args__ = (
        Index('idx_project_created', 'project_id', 'created_at'),
    )
    
    def __repr__(self):
        return f"<ExperimentRun(id={self.id}, project_id={self.project_id}, name='{self.name}')>"


class ExperimentResult(Base):
    """实验结果文件表"""
    __tablename__ = "experiment_results"
    
    id = Column(Integer, primary_key=True, index=True)
    experiment_run_id = Column(Integer, ForeignKey("experiment_runs.id", ondelete="CASCADE"), nullable=False, index=True)
    name = Column(String(200), nullable=False, comment="结果文件名称")
    file_type = Column(String(50), nullable=False, comment="文件类型：image, excel, csv, other")
    file_path = Column(Text, nullable=False, comment="服务器文件路径")
    file_size = Column(Integer, comment="文件大小（字节）")
    description = Column(Text, comment="文件描述")
    created_at = Column(DateTime, default=lambda: datetime.now(), comment="创建时间")
    
    # 关系
    experiment_run = relationship("ExperimentRun", back_populates="results")
    
    # 索引
    __table_args__ = (
        Index('idx_experiment_results_run', 'experiment_run_id'),
        Index('idx_experiment_results_file_type', 'file_type'),
    )
    
    def __repr__(self):
        return f"<ExperimentResult(id={self.id}, experiment_run_id={self.experiment_run_id}, name='{self.name}', file_type='{self.file_type}')>"


class ExperimentDataCategory(Base):
    """数据分类表"""
    __tablename__ = "experiment_data_category"
    
    id = Column(Integer, primary_key=True, index=True)
    experiment_run_id = Column(Integer, ForeignKey("experiment_runs.id", ondelete="CASCADE"), nullable=False, index=True)
    name = Column(String(200), nullable=False, comment="分类名称（如：财务指标, 运营指标, 技术指标）")
    description = Column(Text, comment="分类描述")
    sort_order = Column(Integer, default=0, comment="排序顺序")
    created_at = Column(DateTime, default=lambda: datetime.now(), comment="创建时间")
    
    # 关系
    experiment_run = relationship("ExperimentRun", back_populates="categories")
    series_data = relationship("ExperimentDataSeries", back_populates="category")
    scalar_data = relationship("ExperimentDataScalar", back_populates="category")
    
    # 约束和索引
    __table_args__ = (
        UniqueConstraint('experiment_run_id', 'name', name='uq_experiment_category_name'),
        Index('idx_experiment_category_run', 'experiment_run_id'),
        Index('idx_experiment_category_name', 'name'),
    )
    
    def __repr__(self):
        return f"<ExperimentDataCategory(id={self.id}, experiment_run_id={self.experiment_run_id}, name='{self.name}')>"


class ExperimentDataSeries(Base):
    """序列数据表"""
    __tablename__ = "experiment_data_series"
    
    id = Column(Integer, primary_key=True, index=True)
    experiment_run_id = Column(Integer, ForeignKey("experiment_runs.id", ondelete="CASCADE"), nullable=False, index=True)
    category_id = Column(Integer, ForeignKey("experiment_data_category.id", ondelete="SET NULL"), nullable=True, index=True)
    name = Column(String(200), nullable=False, comment="指标名称（如：收益率, 投资额）")
    data_group = Column(String(200), comment="数据分组（用于逻辑组织）")
    parquet_path = Column(Text, nullable=False, comment="Parquet文件路径（服务器端和本地restore/目录）")
    unit = Column(String(50), comment="指标单位（如：%, 万元）")
    description = Column(Text, comment="指标描述")
    index_column_name = Column(String(50), comment="索引列名：year, month, day, index等（默认index）")
    data_length = Column(Integer, nullable=False, comment="数据长度（连续序列的数量）")
    created_at = Column(DateTime, default=lambda: datetime.now(), comment="创建时间")
    
    # 关系
    experiment_run = relationship("ExperimentRun")
    category = relationship("ExperimentDataCategory", back_populates="series_data")
    
    # 约束和索引
    __table_args__ = (
        UniqueConstraint('experiment_run_id', 'category_id', 'name', name='uq_experiment_series_name'),
        Index('idx_experiment_series_run', 'experiment_run_id'),
        Index('idx_experiment_series_category', 'category_id'),
        Index('idx_experiment_series_name', 'name'),
        Index('idx_experiment_series_group', 'data_group'),
        Index('idx_experiment_series_length', 'data_length'),
    )
    
    def __repr__(self):
        return f"<ExperimentDataSeries(id={self.id}, experiment_run_id={self.experiment_run_id}, name='{self.name}', category_id={self.category_id})>"


class ExperimentDataScalar(Base):
    """单值数据表"""
    __tablename__ = "experiment_data_scalar"
    
    id = Column(Integer, primary_key=True, index=True)
    experiment_run_id = Column(Integer, ForeignKey("experiment_runs.id", ondelete="CASCADE"), nullable=False, index=True)
    category_id = Column(Integer, ForeignKey("experiment_data_category.id", ondelete="SET NULL"), nullable=True, index=True)
    name = Column(String(200), nullable=False, comment="指标名称（如：总项目数, 总装机容量, 总投资额）")
    data_group = Column(String(200), comment="数据分组（用于灵活组合，用户可以任意组合）")
    value = Column(Text, nullable=False, comment="指标值（JSON格式，支持各种数据类型）")
    unit = Column(String(50), comment="指标单位（如：个, MW, 万元）")
    description = Column(Text, comment="指标描述")
    created_at = Column(DateTime, default=lambda: datetime.now(), comment="创建时间")
    
    # 关系
    experiment_run = relationship("ExperimentRun")
    category = relationship("ExperimentDataCategory", back_populates="scalar_data")
    
    # 约束和索引
    __table_args__ = (
        UniqueConstraint('experiment_run_id', 'category_id', 'name', name='uq_experiment_scalar_name'),
        Index('idx_experiment_scalar_run', 'experiment_run_id'),
        Index('idx_experiment_scalar_category', 'category_id'),
        Index('idx_experiment_scalar_name', 'name'),
        Index('idx_experiment_scalar_group', 'data_group'),
    )
    
    def __repr__(self):
        return f"<ExperimentDataScalar(id={self.id}, experiment_run_id={self.experiment_run_id}, name='{self.name}', category_id={self.category_id})>"

